import pandas as pd


meta_data = pd.read_excel("./meta/meta.xlsx", index_col=0, sheet_name='meta')
meta_data_dic = meta_data.iloc[:,0].to_dict()

data_collector = {}

meta_collector = {}

for cn, en in meta_data_dic.items():
    print(cn, en)
    path = f"../wid_data/WID_data_{en}.csv"
    meta_path = f"../wid_data/WID_metadata_{en}.csv"
    try:
        df = pd.read_csv(path, delimiter=';')
        data_collector[cn] = df
    except:
        print(f"Error: {path}")
    try:
        df = pd.read_csv(meta_path, delimiter=';')
        meta_collector[cn] = df
    except:
        print(f"Error: {meta_path}")


def extract_p(df, p='p0p10'):
    cdf = df[df['percentile'] == p]
    return cdf


p10_100 = {}
for country, df in data_collector.items():
    print(country)
    df = df[df['variable'] == 'shwealj992']
    df = df[df['year'] == 2022]
    li = []
    for p in [
        'p0p10', 'p10p20', 'p20p30', 'p30p40', 'p40p50', 
        'p50p60', 'p60p70', 'p70p80', 'p80p90', 'p90p100',
        # 'p90p100',
        # 'p50p90',
        # 'p0p50',
        # 'p99p100',
        ]:
        cdf = extract_p(df, p)
        li.append(cdf)
    df_country_10 = pd.concat(li)
    df_country_10['country'] = country
    if len(df_country_10) != 10:
        print(666)
    df_country_10.index = df_country_10['percentile']
    se = df_country_10['value']
    se.name = country
    p10_100[country] = se

df_p10_100 = pd.DataFrame(p10_100)
df_p10_100.dropna(axis=1, inplace=True)




